package com.jf.cloud.service.sysmanager.common.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.StatementCreatorUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.jf.cloud.service.sysmanager.common.taglib.Paging;

/**
 * 基础DAO类
 * @author 程伟平 创建时间：2012-02-08 15:05:56
 * 
 * 12月4号修改
 * 增加 updateRisk 方法
 */
@Repository("baseDao")
public class BaseDao {

	private static final Log log = LogFactory.getLog(BaseDao.class);
	
	public static int safeNum=1000;
	/**
	 * JDBC 模板
	 */
	@Autowired
	private JdbcTemplate jdbcTemplate;

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	/**
	 * 获取对象（某个属性）列表
	 * 
	 * @author 朱建文 创建时间：2018年4月10日 下午1:36:42
	 */
	public <T> List<T> findList(String sql, Class<T> clazz, Object... args) {
		log.debug("★★★SQL:" + sql);
		log.debug("★★★SQL参数:" + getSqlArgs(args));
		return this.jdbcTemplate.queryForList(sql, clazz, args);
	}
	
	/**
	 * ★ 查询对象列表List<T>。
	 */
	public <T> List<T> findList(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException {
		printLogger(sql, args);
		List<T> rs = null;
		try {
			rs = jdbcTemplate.query(sql, args, rowMapper);
		} catch (EmptyResultDataAccessException e) {
			log.error("★查询数据异常★ "+e.getLocalizedMessage() +" ★其他信息★:"+e.getMessage());
		}
		return rs;
	}

	/**
	 * ★ 分页查询对象列表List<T>。
	 * @author 程伟平 创建时间：2012-02-08 17:40:03
	 */
	public <T> Paging<T> findListByPage(String sql, RowMapper<T> rowMapper, Paging<T> page, Object... args) throws DataAccessException {
		int total = findSingleColumnObject(SqlUtil.getPageCountSQL(sql), Integer.class, args);
		page.setTotalRows(total);
		if (sql != null && sql.length() > 0 && page != null && page.isPaging()) {
			sql += page.getLimitStr();
		}// update by ouyang 0209/change return value to page
		List<T> rsList = findList(sql, rowMapper, args);// jdbcTemplate.query(sql, args, rowMapper);
		page.setDataList(rsList);
		return page;
	}
	/**
	 *  后台 商品列表 查询 专用 切记
	 */
	public <T> Paging<T> findListByPage_goods(String sql, RowMapper<T> rowMapper, Paging<T> page, Object... args) throws DataAccessException {
		if (sql != null && sql.length() > 0 && page != null && page.isPaging()) {
			sql += page.getLimitStr();
		} 
		List<T> rsList = findList(sql, rowMapper, args);
		page.setDataList(rsList);
		return page;
	}
	/**
	 * ★ 分页查询对象列表List<T>。
	 */
	public <T> Paging<T> findListByPage(String sql, String countSql, RowMapper<T> rowMapper, Paging<T> page, Object... args) throws DataAccessException {
		int total = this.findCount(countSql, args);
		page.setTotalRows(total);
		if (sql != null && sql.length() > 0 && page != null && page.isPaging()) {
			sql += page.getLimitStr();
		}// update by ouyang 0209/change return value to page
		List<T> rsList = findList(sql, rowMapper, args);// jdbcTemplate.query(sql, args, rowMapper);
		page.setDataList(rsList);
		return page;
	}
	/**
	 * ★ 查询单个对象Object。
	 */
	public <T> T findObject(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException {
		printLogger(sql, args);
		T rs = null;
		try {
			rs = jdbcTemplate.queryForObject(sql, args, rowMapper);
		} catch (EmptyResultDataAccessException e) {
			log.error("★查询数据异常★ "+e.getLocalizedMessage() +" ★其他信息★:"+e.getMessage());
		}
		return rs;
	}

	/**
	 * ★ 根据主键查询单个对象Object 已启用，请使用findObject方法替代
	 */
	@Deprecated
	public <T> T findByPrimaryKey(String sql, RowMapper<T> rowMapper, Object pk) throws DataAccessException {
		return findObject(sql, rowMapper, pk);
	}

	/**
	 * 获取对象（某个属性）
	 * 
	 * @author 程伟平 创建时间：2013-02-20
	 */
	public <T> T find(String sql, Class<T> clazz, Object... args) {
		log.debug("★★★SQL:" + sql);
		log.debug("★★★SQL参数:" + getSqlArgs(args));
		
		T rs = null;
		try {
			rs = this.jdbcTemplate.queryForObject(sql, clazz, args);
		} catch (EmptyResultDataAccessException e) {
			log.error("★查询数据异常★ " + e.getLocalizedMessage() + " ★其他信息★:" + e.getMessage());
		}
		return rs;
	}
	
	/**
	 * ★ Object 查询单个字段列表。
	 */
	public <T> List<T> findSingleColumnList(String sql, Class<T> clazz, Object... args) throws DataAccessException {
		printLogger(sql, args);
		List<T> rs = null;
		try {
			rs = jdbcTemplate.queryForList(sql, args, clazz);
		} catch (EmptyResultDataAccessException e) {
			log.error("★查询数据异常★ "+e.getLocalizedMessage() +" ★其他信息★:"+e.getMessage());
		}
		return rs;
	}

	/**
	 * ★ Object 查询单个字段 返回 对象类型 如 分页的count int
	 */
	public <T> T findSingleColumnObject(String sql, Class<T> clazz, Object... args) throws DataAccessException {
		printLogger(sql, args);
		T rs = null;
		try {
			rs = jdbcTemplate.queryForObject(sql, args, clazz);
		} catch (EmptyResultDataAccessException e) {
			log.error("★查询数据异常★ "+e.getLocalizedMessage() +" ★其他信息★:"+e.getMessage());
		}
		return rs;
	}

	/**
	 * 查询数据总数
	 * 
	 * @author ouyang 创建时间：Mar 29, 2012 3:53:55 PM
	 */
	public Integer findCount(String sql, Object... args) throws DataAccessException {
		return findSingleColumnObject(sql, Integer.class, args);
	}

	/**
	 * ★ 查询Map列表List<Map<String, Object>> 键为字段名称（大写） 值是object
	 */
	public List<Map<String, Object>> findMapList(String sql) throws DataAccessException {
		printLogger(sql, null);
		List<Map<String, Object>> rs = null;
		try {
			rs = jdbcTemplate.queryForList(sql);
		} catch (EmptyResultDataAccessException e) {
			log.error("★查询数据异常★ "+e.getLocalizedMessage() +" ★其他信息★:"+e.getMessage());
		}
		return rs;
	}

	/**
	 * ★ 根据参数查询Map列表List<Map<String, Object>> 键为字段名称（大写） 值是object
	 */
	public List<Map<String, Object>> findMapList(String sql, Object... args) throws DataAccessException {
		printLogger(sql, args);
		List<Map<String, Object>> rs = null;
		try {
			rs = jdbcTemplate.queryForList(sql, args);
		} catch (EmptyResultDataAccessException e) {
			log.error("★查询数据异常★ "+e.getLocalizedMessage() +" ★其他信息★:"+e.getMessage());
		}
		return rs;
	}

	/**
	 * ★ 查询单个对象Map<ColumnName,ColumnValue> 仅针对 字段。
	 */
	public Map<String, Object> findMap(String sql, Object... args) throws DataAccessException {
		printLogger(sql, args);
		Map<String, Object> rs = null;
		try {
			rs = jdbcTemplate.queryForMap(sql, args);
		} catch (EmptyResultDataAccessException e) {
			log.error("★查询数据异常★ "+e.getLocalizedMessage() +" ★其他信息★:"+e.getMessage());
		}
		return rs;
	}

	/**
	 * ★ 根据参数分页查询Map列表List<Map<String, Object>> 键为字段名称（大写） 值是object
	 */
	public Paging<Map<String, Object>> findObjectListMapByPage(String sql, Paging<Map<String, Object>> page, Object... args)
			throws DataAccessException {
		int total = findSingleColumnObject(SqlUtil.getPageCountSQL(sql), Integer.class, args);
		page.setTotalRows(total);
		if (sql != null && sql.length() > 0 && page != null && page.isPaging()) {
			sql += " " + page.getLimitStr();
		} // update by ouyang 0209/change return value to page
		List<Map<String, Object>> rsList =findMapList(sql, args);// jdbcTemplate.queryForList();
		page.setDataList(rsList);
		return page;

	}

	/**
	 * ★ 根据SQL语句执行保存、更新或删除
	 */
	public int update(String sql) throws DataAccessException {
		printLogger(sql, null);
		int num = jdbcTemplate.update(sql);
		if(num<1){
			throw new RuntimeException("★★★★没有修改任何数据★★★★");
		}
		this.check(num);
		return num;
	}
	/**
	 * ★ 根据SQL语句执行保存、更新或删除 RuntimeException
	 * 【无事物，不关心结果的修改，有风险的】
	 */
	public int updateRisk(String sql) throws RuntimeException {
		printLogger(sql, null);
		int num = jdbcTemplate.update(sql);
		return num;
	}

	/**
	 * ★ 根据SQL语句执行 【保存数据并返回 主键ID AutoIncrement 】 注意 主键必须是自增的才有效
	 * @return int
	 */
	public Long save(final String sql, final List<Object> params) throws DataAccessException {
		printLogger(sql, params.toArray());
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
				PreparedStatement ps = (PreparedStatement) connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				for (int i = 0; params != null && i < params.size(); i++) {
					StatementCreatorUtils.setParameterValue(ps, i + 1, -2147483648, params.get(i));
				}
				return ps;
			}
		}, keyHolder);
		long id = keyHolder.getKey().longValue();
		return id;
	}

	/**
	 * ★ 根据SQL语句及参数执行保存、更新或删除
	 * @throws DataAccessException
	 */
	public int update(String sql, Object... args) throws DataAccessException {
		printLogger(sql, args);
		int num = jdbcTemplate.update(sql, args);
		if(num<1){
			throw new RuntimeException("★★★★没有修改任何数据★★★★");
		}
		this.check(num);
		return num;
	}
	/**
	 * ★ 根据SQL语句及参数执行保存、更新或删除 【无事物，不关心结果的修改，有风险的】
	 */
	public int updateRisk(String sql, Object... args) throws RuntimeException {
		printLogger(sql, args);
		int num = jdbcTemplate.update(sql, args);
		//this.check(num); bgy2015-03-19注释
		return num;
	}

	/**
	 * 执行修改语句！返回执行结果 boolean (指定必须影响的行数)
	 * @param mustUpdateRecords 预期的执行影响条数 如果大于0 并且和执行结果不相等则抛出异常 (null 或者 0 则不做要求)
	 * @author ouyang 创建时间：Feb 10, 2012 5:52:47 AM
	 */
	public Boolean safeUpdate(String sql, int mustUpdateRecords, Object... args) throws DataAccessException {
		printLogger(sql, args);
		int num = jdbcTemplate.update(sql, args);
		if (mustUpdateRecords > 0 && mustUpdateRecords != num) {
			num = -1;
			throw new RuntimeException("修改记录条数和指定值不相等!当前修改num=" + num + " 预期修改:mustUpdateRecords=" + mustUpdateRecords);
		}
		this.check(num);
		return num > 0 ? true : false;
	}

	/**
	 * 新增对象
	 * 
	 * @author 程伟平 创建时间：2014-05-17 19:21:22
	 */
	public <T> T saveAll(final T o) throws DataAccessException {
		final String insertSql = EntityParser.getInsertSql(o.getClass());
		KeyHolder keyHolder = new GeneratedKeyHolder();
		int num = jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
				List<Object> valueList = EntityParser.getValueList(o, false);
				if (valueList != null && valueList.size() > 0) {
					int i = 0;
					for (Object value : valueList) {
						// 字符串
						if (value instanceof String) {
							ps.setString(++i, value == null ? null : value.toString());
						}
						// 长整型
						else if (value instanceof Long) {
							ps.setLong(++i, value == null ? 0 : Long.valueOf(value.toString()));
						}
						// 整型
						else if (value instanceof Integer) {
							ps.setInt(++i, value == null ? 0 : Integer.valueOf(value.toString()));
						}
						// 单精度浮点数
						else if (value instanceof Float) {
							ps.setFloat(++i, value == null ? 0 : Float.valueOf(value.toString()));
						}
						// 双精度浮点数
						else if (value instanceof Double) {
							ps.setDouble(++i, value == null ? 0 : Double.valueOf(value.toString()));
						}
						// 字符串
						else {
							ps.setString(++i, value == null ? null : value.toString());
						}
					}
				}
				return ps;
			}
		}, keyHolder);
		if (1 != num) {
			throw new IncorrectResultSizeDataAccessException("不正确的更新结果数！期望更新数【" + 1 + "】，实际更新数【" + num + "】", 1, num);
		}
		// 设置主键值
		EntityParser.setPkValue(o, keyHolder.getKey().longValue());
		return o;
	}

	/**
	 * 更新对象（全字段更新）
	 * 
	 * @author 程伟平 创建时间：2014-05-17 19:21:22
	 */
	@Deprecated
	public <T> T updateAll(final T o) throws DataAccessException {
		final String updateSql = EntityParser.getUpdateSql(o.getClass());
		final List<Object> valueList = EntityParser.getValueList(o, false);
		printLogger(updateSql, valueList.toArray());
		int num = jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(updateSql, Statement.RETURN_GENERATED_KEYS);
				if (valueList != null && valueList.size() > 0) {
					int i = 0;
					for (Object value : valueList) {
						// 字符串
						if (value instanceof String) {
							ps.setString(++i, value == null ? null : value.toString());
						}
						// 长整型
						else if (value instanceof Long) {
							ps.setLong(++i, value == null ? 0 : Long.valueOf(value.toString()));
						}
						// 整型
						else if (value instanceof Integer) {
							ps.setInt(++i, value == null ? 0 : Integer.valueOf(value.toString()));
						}
						// 单精度浮点数
						else if (value instanceof Float) {
							ps.setFloat(++i, value == null ? 0 : Float.valueOf(value.toString()));
						}
						// 双精度浮点数
						else if (value instanceof Double) {
							ps.setDouble(++i, value == null ? 0 : Double.valueOf(value.toString()));
						}
						// 字符串
						else {
							ps.setString(++i, value == null ? null : value.toString());
						}
					}

					// 主键
					Object value = EntityParser.getPkValue(o);
					ps.setLong(++i, value == null ? -1 : Long.valueOf(value.toString()));
				}
				return ps;
			}
		});
		if (1 != num) {
			throw new IncorrectResultSizeDataAccessException("不正确的更新结果数！期望更新数【" + 1 + "】，实际更新数【" + num + "】", 1, num);
		}
		return o;
	}

	/**
	 * ★ 打印日志
	 * 
	 * @author 程伟平 创建时间：2012-02-08 17:45:02
	 * @param sql SQL语句
	 * @param args 参数
	 */
	protected void printLogger(String sql, Object[] args) {
		StringBuffer sb = new StringBuffer();
		if (args != null && args.length > 0) {
			for (int i = 0; i < args.length; i++) {
				if (sb.length() > 0) {
					sb.append(",");
				}
				sb.append(args[i]);
			}
		}
		log.info("★★sql:" + sql + "★★参数:" + sb.toString());
	}
	/**
	 * TODO  数据安全检查
	 */
	protected void check(int num) {
		if(num>safeNum){ 
			throw new RuntimeException("★★★★修改 数据量过大★★★★");
		}  
	}
	
	/**
	 * 获取SQL参数
	 * 
	 * @author 程伟平 创建时间：2012-02-08 17:45:02
	 */
	private String getSqlArgs(Object[] sqlArgs) {
		StringBuffer sb = new StringBuffer();
		if (sqlArgs != null && sqlArgs.length > 0) {
			for (int i = 0; i < sqlArgs.length; i++) {
				if (sb.length() > 0) {
					sb.append(",");
				}
				sb.append(sqlArgs[i]);
			}
		}
		return sb.toString();
	}
}
